# @Time    : 2019/8/15 16:37
# @Author  : patrick
# @File    : mysqlHelper.py
# @Software: PyCharm
import pymysql

import readConfig
localReadConfig = readConfig.ReadConfig()


class MysqlHelper:
    def __init__(self):
        self.host = localReadConfig.get_database("host")
        self.user = localReadConfig.get_database("username")
        self.password = localReadConfig.get_database("password")
        self.db = localReadConfig.get_database("database")
        self.port = localReadConfig.get_database("port")

    def __getCursor(self):
        try:
            self.conn = pymysql.connect(
                host=self.host,
                port=int(self.port),
                user=self.user,
                password=self.password,
                database=self.db,
                charset="utf8",
                cursorclass=pymysql.cursors.DictCursor
            )
        except Exception as e:
            print("DataBase Error : %s" % e)
        finally:
            self.cursor = self.conn.cursor()

    def excuteQuery(self, sql):
        try:
            self.__getCursor()
            # print("开启数据库连接。")
            self.cursor.execute(sql)
            return self.cursor.fetchone()
        except Exception as e:
            print("Query Error : %s" % e)
        finally:
            self.cursor.close()
            self.conn.close()
            # print("关闭数据库连接。")

    def queryBasicPremium(self, productID, coverage):
        # print("premium")
        premium = 0.00
        coverage = int(coverage) / 1000
        # print(coverage)
        for i in range(0, productID.__len__()):
            basic_sql = "select rate from t_basic_premium_rate where id = '%s'" % productID[i]
            # print(basic_sql)
            result = self.excuteQuery(basic_sql)
            # print(result)
            if result is None:
                premium += 0
            else:
                premium += float(coverage) * float(result["rate"])
        # print("基本费率："+str(premium))
        return premium

    def queryJobAdd(self, jobID, coverage):
        # print("Add")
        premium = 0.00
        coverage = int(coverage) / 1000
        for i in range(0, jobID.__len__()):
            sql = "select rate from t_job_premium_rate where id = '%s'" % jobID[i]
            result = self.excuteQuery(sql)
            # print(result)
            if result is None:
                premium += 0
            else:
                premium += float(coverage) * float(result["rate"])
        # print("职业加费："+str(premium))
        return premium


if __name__ == '__main__':
    conn = MysqlHelper()
    conn.queryJobAdd(['LW05|A2|0545','MIP1|A2|1682'], 50000)
    conn.queryBasicPremium(["AS01|A1|10|M|10"], 50000)
    # result = conn.excuteQuery("select * from t_basic_premium_rate where relation_code = 'LW06'")
    # for i in result:
    #     print(i)
